package common;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.sql.*;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
 * @Author Pelle Ravn
 * @Version 2006.10.02 - revised marts 2009 FEN
 * @version 2010.10.07 - new connection
 * @version 2011.03.30 - Completely new version of the class with settings file
 * This class is used to create the connection to the database
 * It is implementet as a singelton. The constructor is private to encure that only
 * one object of the class is generated
 * Version for Sql Server or MySql lines in comments
 */

public class DbConnection
{
    //SQL Server
    private static final String DRIVER = "jdbc:sqlserver://";
    private static final String CLASSNAME = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    private static final int TIMEOUT = 5;

    // Settings from settingsfile
    private static HashMap<String, String> settings = new HashMap<String, String>();

    // If it shall show debug informations
    private static boolean DEBUG = false;

    private DatabaseMetaData dma;
    private Connection con;
    // an instance of the class is generetated
    private static DbConnection  instance = new DbConnection();

    private int lastId = -1;

    // the constructor is private to ensure that only one object of this class is createt
    private DbConnection()
    {
        this.getSettings();
    	String url = DRIVER + settings.get("server") + ";databaseName=" + settings.get("database") + ";user=" + settings.get("username") + ";password=" + settings.get("password");

        try{
            //load af driver
            //SQL Server
            Class.forName(CLASSNAME);
            if(DEBUG) {
                System.out.println("Load af class ok + (" + CLASSNAME + ")");
            }

        }
        catch(Exception e){
            if(DEBUG) {
                System.out.println("Can not find the driver");
                System.out.println(e.getMessage());
            }
        }//end catch
        try{
            //connection to the database
            con = DriverManager.getConnection(url);

            //set autocommit
            con.setAutoCommit(true);

            if(DEBUG) {
                dma = con.getMetaData(); // get meta data
                
                System.out.println("Connection to " + dma.getURL());
                System.out.println("Driver " + dma.getDriverName());
                System.out.println("Database product name " + dma.getDatabaseProductName());
            }
        }//end try
        catch(Exception e){
            if(DEBUG) {
                System.out.println("Problems with the connection to the database");
                System.out.println(e.getMessage());
                System.out.println(url);
            }
        }//end catch
    }//end  constructor

    //this method is used to get the instance of the connection
    public static DbConnection getInstance()
    {
        return instance;
    }

    public void getSettings() {
        // Only runs if it haven't been run yet
        if(settings.isEmpty()) {
            try {
                File f = new File("settings.conf");
                if(f.exists()) {
                    FileReader in = new FileReader("settings.conf");
                    BufferedReader br = new BufferedReader(in);
                    String strLine;

                    //Read File Line By Line
                    while ((strLine = br.readLine()) != null)   {
                        if(strLine.contains("debug") && strLine.contains("true")) {
                            this.DEBUG = true;
                        } else if(!strLine.startsWith("#")) {
                            String name = strLine.split("=")[0].trim();
                            String value = strLine.split("=")[1].trim();

                            settings.put(name, value);
                        }
                    }

                    //Close the input stream
                    br.close();
                } else {
                    System.out.println("No settingsfile!");
                }

            } catch (IOException ex) {
                Logger.getLogger(DbConnection.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public ResultSet query(String queryString) {
        ResultSet rs = null;

        // Checking if there is a connection to the database
        if(con != null) {
            try {
                Statement statement = con.createStatement();
                statement.setQueryTimeout(TIMEOUT);

                this.printSQLDebug(queryString);

                rs = statement.executeQuery(queryString);
            } catch (SQLException ex) {
                if(DEBUG) {
                    System.out.println(DbConnection.class.getName() + " (" + Level.SEVERE + "): " + ex);
                }
            }
        }

        return rs;
    }

    public int queryUpdate(String queryString) {
        return this.queryUpdate(queryString, false);
    }

    private int queryUpdate(String queryString, boolean getKeys) {
        Statement statement;
        int c = -1;

        if(con != null) {
            try {
                statement = con.createStatement();
                statement.setQueryTimeout(TIMEOUT);

                this.printSQLDebug(queryString);

                c = statement.executeUpdate(queryString, Statement.RETURN_GENERATED_KEYS);

                if(getKeys) {
                    ResultSet rsId = statement.getGeneratedKeys();

                    if (rsId != null && rsId.next()) {
                        this.lastId = rsId.getInt(1);
                    }
                }

            } catch (SQLException ex) {
                Logger.getLogger(DbConnection.class.getName()).log(Level.SEVERE, null, ex);
                if(DEBUG) {
                    System.out.println(DbConnection.class.getName() + " (" + Level.SEVERE + "): " + ex);
                }
            }
        }

        return c;
    }

    public int getInsertId() {
        return this.lastId;
    }

    public int queryInsert(String queryString) {
        return queryUpdate(queryString, true);
    }

    public int queryDelete(String queryString) {
        return queryUpdate(queryString, false);
    }

    public boolean executeTransaction(ArrayList<String> statements) {
        boolean success = false;
        boolean die = false;
        Statement statement;

        if(statements.size() > 0) {
            try {
                statement = con.createStatement();
                statement.setQueryTimeout(TIMEOUT);

                if(DEBUG) {
                    System.out.println("======= Start på transaktion =======");
                }
                con.setAutoCommit(false);

                int i = 1;
                for(String st : statements) {
                    if(DEBUG) {
                        System.out.println("Statement #" + i + "");
                    }

                    this.printSQLDebug(st);
                    
                    if(statement.executeUpdate(st) == -1) {
                        die = true;
                        break;
                    }

                    i++;
                }

                statement.close();

                if(!die) {
                    if(DEBUG) {
                        System.out.println(" - Committer!");
                    }
                    
                    con.commit();
                    success = true;

                } else {
                    if(DEBUG) {
                        System.out.println(" Fejl opstået. Forsøger rollback!");
                    }

                    con.rollback();
                    success = false;
                }

                con.setAutoCommit(true);
                

                if(DEBUG) {
                    System.out.println("======= Slut på transaktion =======");
                }

            } catch (SQLException ex) {
                Logger.getLogger(DbConnection.class.getName()).log(Level.SEVERE, null, ex);
                if(DEBUG) {
                    System.out.println("Transaction fejlede!; " + DbConnection.class.getName() + " (" + Level.SEVERE + "): " + ex);
                }
                try {
                    con.rollback();
                    con.setAutoCommit(true);
                } catch (SQLException ex1) {
                    Logger.getLogger(DbConnection.class.getName()).log(Level.SEVERE, null, ex1);
                    
                    System.out.println("Rollback faild. Call techinal assistant! " + DbConnection.class.getName() + " (" + Level.SEVERE + "): " + ex);
                    
                }
            }
        }
        
        return success;
    }

    public void printSQLDebug(String queryString) {
        if(DEBUG) {
            System.out.println(queryString);
        }
    }

  //closeDb: closes the connection to the database
    public void closeConnection()
    {
       	try{
            con.close();
            if(DEBUG) {
                System.out.println("The connection is closed");
            }
        }
         catch (Exception e){
            if(DEBUG) {
                System.out.println("Error trying to close the database " +  e.getMessage());
            }
         }
    }//end closeDB


}//end DbConnection
